import os
import pandas as pd
from collections import defaultdict

# 指定目录

directory = r'D:\xlzx\4G5G资料20220610\2025\926'
stock_file = r'D:\xlzx\4G5G资料20220610\2025\926\站址存量表20250926.xlsx'

df_stock = pd.read_excel(stock_file)

# 存储结果的字典
result_dict = defaultdict(lambda: {'count': 0})

# 遍历目录中的所有文件
for filename in os.listdir(directory):
    if filename.endswith('.xlsx') and filename != 'result.xlsx':  # 排除生成的结果文件
        file_path = os.path.join(directory, filename)
        # 读取Excel文件
        try:
            df = pd.read_excel(file_path)
            # 统计所属站址编码和所属站址编码INTID中重复出现的值
            for index, row in df.iterrows():
                site_code = row.get('所属站址编码')
                site_code_intid = row.get('所属站址编码INTID')
                if site_code:
                    result_dict[site_code]['count'] += 1
                    if filename not in result_dict[site_code]:
                        result_dict[site_code][filename] = 0
                    result_dict[site_code][filename] += 1
                if site_code_intid:
                    result_dict[site_code_intid]['count'] += 1
                    if filename not in result_dict[site_code_intid]:
                        result_dict[site_code_intid][filename] = 0
                    result_dict[site_code_intid][filename] += 1
        except Exception as e:
            print(f"Error reading {file_path}: {e}")

# 将结果转换为DataFrame
data = []
for value, info in result_dict.items():
    row = {'Value': value, 'Count': info['count']}
    row.update({file: info.get(file, 0) for file in os.listdir(directory) if file.endswith('.xlsx') and file != 'result.xlsx'})  # 排除生成的结果文件
    data.append(row)

df_result = pd.DataFrame(data)

# 将结果保存到Excel文件
output_file_path = os.path.join(directory, 'result.xlsx')
df_result = df_result.rename(columns={'Value': '站址编码INTID'})
# 新增代码部分：读取存量表并关联数据
# -------------------------------------------------


# 使用左连接合并数据（保留结果表所有记录）
df_merged = pd.merge(
    df_result,
    df_stock[['站址编码INTID', '站址名称', '铁塔站址编码', '铁塔站址名称']],  # 选择需要的列
    on='站址编码INTID',
    how='left'
)

# 调整列顺序（将新增列插入到站址编码INTID后）
cols = df_merged.columns.tolist()
new_cols = (
    ['站址编码INTID', '站址名称', '铁塔站址编码', '铁塔站址名称'] 
    + [col for col in cols if col not in {'站址编码INTID', '站址名称', '铁塔站址编码', '铁塔站址名称'}]
)
df_result = df_merged[new_cols]
df_result.to_excel(output_file_path, index=False)

print(f'结果已保存到 {output_file_path}')